package com.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.beans.Clothesbean;
import com.beans.Msgbean;
import com.beans.Userbean;

public class DB {
	private Connection con;
	private String sql;
	private Statement stmt;
	private ResultSet rs;
	private String user = "dong";
	private String password = "xiaodong";
	// private String className = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
	// private String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=vk";
	private String className = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://localhost/web_pro_vk";

	public DB() {
		try {
			Class.forName(className).newInstance();
		} catch (ClassNotFoundException e) {
			System.out.println("加载数据库驱动失败！");
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
	}

	/** 创建数据库连接 */
	public Connection getCon() {
//		if (con == null) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				con = DriverManager.getConnection(url, user, password);
			} catch (SQLException | ClassNotFoundException e) {
				System.out.println("创建数据库连接失败！");
				con = null;
				e.printStackTrace();
			}
//		}
		return con;
	}

	/**
	 * 通过用户名获取用户信息
	 * */
	public Userbean getUser(String username) {
		Userbean user = new Userbean();
		sql = "select * from vk01_users where username='" + username + "'";
		con = getCon();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				user.setUsername(rs.getString(1));
				user.setPassword(rs.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}

	/**
	 * 判断用户名是否存在
	 */
	public boolean getUserName(String username) {
		sql = "select * from vk_users where username='" + username + "'";
		con = getCon();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 添加用户
	 */
	public void setUser(Userbean user) {
		sql = "insert into vk01_users values('" + user.getUsername() + "','"
				+ user.getPassword() + "','" + user.getEmail() + "')";
		con = getCon();
		try {
			stmt = con.createStatement();
			System.out.println(sql);
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 查询所有留言
	 */
	public ArrayList<Msgbean> getMsg() {
		sql = "select * from vk01_message order by time desc";
		con = getCon();
		ArrayList<Msgbean> msglist = new ArrayList<Msgbean>();
		try {
			stmt = con.createStatement();
			// System.out.println(sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Msgbean msg = new Msgbean();
				msg.setUsername(rs.getString(1));
				msg.setMsg(rs.getString(2));
				msg.setTime(rs.getString(3));
				// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));
				msglist.add(msg);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return msglist;
	}

	/**
	 * 分页查询留言
	 */
	public ArrayList<Msgbean> getPageMsg(int pageno, int pagesize) {
		sql = "select * from vk01_message order by time desc";
		con = getCon();
		int row = (pageno - 1) * pagesize + 1;
		ArrayList<Msgbean> msglist = new ArrayList<Msgbean>();
		try {
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			// System.out.println(sql);
			rs = stmt.executeQuery(sql);
			rs.absolute(row);
			for (int i = 0; i < pagesize; i++) {
				Msgbean msg = new Msgbean();
				msg.setUsername(rs.getString(1));
				msg.setMsg(rs.getString(2));
				msg.setTime(rs.getString(3));
				// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));
				msglist.add(msg);
				if (!rs.next())
					break;
			}
			// while(rs.next()){
			// Msgbean msg = new Msgbean();
			// msg.setUsername(rs.getString(1));
			// msg.setMsg(rs.getString(2));
			// msg.setTime(rs.getString(3));
			// //
			// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));
			// msglist.add(msg);
			// i++;
			// if(i>pagesize) break;
			// }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return msglist;
	}

	/**
	 * 查询热门留言
	 */
	public ArrayList<Msgbean> getRMsg() {
//		sql = "select * from vk01_message where v_remen=1 order by v_time desc";
		sql = "select * from vk01_message where hot=1 order by time desc";
		con = getCon();
		ArrayList<Msgbean> msglist = new ArrayList<Msgbean>();
		try {
			stmt = con.createStatement();
			// System.out.println(sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Msgbean msg = new Msgbean();
				msg.setUsername(rs.getString(1));
				msg.setMsg(rs.getString(2));
				msg.setTime(rs.getString(3));
				// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));
				msglist.add(msg);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return msglist;
	}

	/**
	 * 增加留言
	 */
	public int addMsg(Msgbean msgb) {
		int i = 0;
		if (msgb == null) {
			return i;
		}
		sql = "insert into vk01_message values('" + msgb.getUsername() + "','"
				+ msgb.getMsg() + "','" + msgb.getTime() + "','" + 0 + "')";
		con = getCon();
		try {
			stmt = con.createStatement();
			i = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	/**
	 * 获取留言记录数
	 */
	public int getMsgnum() {
		int num = 0;
		sql = "select count(*) as recordcount from vk01_message";
		con = getCon();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next())
				num = rs.getInt("recordcount");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}

	/**
	 * 通过产品编号获取商品
	 * 
	 * @param cid
	 * @return
	 */
	public Clothesbean getClothes(String cid) {
		sql = "select * from vk01_clothes where cid='" + cid + "'";
		con = getCon();
		Clothesbean cb = new Clothesbean();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				cb.setCid(cid);
				cb.setPrice(rs.getString(2));
				cb.setCnum(rs.getInt(3));
				return cb;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 释放Statement对象与Connection对象
	 */
	public void closed() {
		try {
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
			System.out.println("关闭stmt对象失败！");
			e.printStackTrace();
		}
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			System.out.println("关闭con对象失败！");
			e.printStackTrace();
		}
	}

}
